package cn.edu.zafu.bigdata.hdb;
import java.sql.*;

public class DatabaseManager {
    private static final String URL = "jdbc:mysql://192.168.169.76:3306/learnjava";
    private static final String USER = "learn";
    private static final String PASSWORD = "learnpassword";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void addStudent(Student student) throws SQLException {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement("INSERT INTO student202211080113 (id, name, phone) VALUES (?, ?, ?)")) {
            pstmt.setString(1, student.getId());
            pstmt.setString(2, student.getName());
            pstmt.setString(3, student.getPhone());
            pstmt.executeUpdate();
        }
    }

    public static Student getStudent(String id) throws SQLException {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM student202211080113 WHERE id = ?")) {
            pstmt.setString(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return new Student(rs.getString("id"), rs.getString("name"), rs.getString("phone"));
            }
        }
        return null;
    }

    public static void updateStudent(Student student) throws SQLException {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement("UPDATE student202211080113 SET name = ?, phone = ? WHERE id = ?")) {
            pstmt.setString(1, student.getName());
            pstmt.setString(2, student.getPhone());
            pstmt.setString(3, student.getId());
            pstmt.executeUpdate();
        }
    }

    public static void deleteStudent(String id) throws SQLException {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement("DELETE FROM student202211080113 WHERE id = ?")) {
            pstmt.setString(1, id);
            pstmt.executeUpdate();
        }
    }

    // 连接数据库并查询表名
    public static void showtables() throws SQLException {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SHOW TABLES")) {

            System.out.println("数据库 'learnjava' 中的表:");
            while (rs.next()) {
                // 获取当前行的表名
                String tableName = rs.getString(1);
                System.out.println(tableName);
            }
        } catch (Exception e) {
            System.out.println("数据库连接失败或查询失败:");
            e.printStackTrace();
        }
    }

    //建表
    public static void createTable(String tab) {

        //注入风险
        String createTableSQL = "CREATE TABLE IF NOT EXISTS "+tab+" (" +
                "id VARCHAR(255) NOT NULL, " +
                "name VARCHAR(255), " +
                "phone VARCHAR(255), " +
                "PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(createTableSQL)) {
            //pstmt.setString(1, tab);
            pstmt.executeUpdate();
            System.out.println("Table '"+tab+"' created or already exists.");

        } catch (SQLException e) {
            System.out.println("Error creating table:");
            e.printStackTrace();
        }
    }

    //删表功能
    //OOTD
}